Recommended Optimizations View

In this view all Recommended Optmizations of the CData Virtuality Server are displayed.

As the BI / Reporting tools start generating reports by issuing queries to the CData Virtuality Server, the latter monitors the queries' behavior and gathers statistics about the frequency of the used query structures.

• For each query, CData Virtuality Server creates a number of Recommended Optimizations. Optimizations have a Priority (column P) that is denoted by a colored star and can be configured in the Preferences Dialog. Green color means that the corresponding query is rarely issued, yellow that it is issued often and red that it is issued very often.
• Optimizations can be enabled (column A), so that CData Virtuality Server will create or modify the Analytical Storage tables accordingly. As soon as data has been transferred to the Analytical Storage, the reports start executing faster due to an optimized table structure in the Analytical Storage. To keep the Analytical Storage structure up-to-date, schedule Optimizations to be run periodically in accordance to the frequency of data changes of the corresponding source tables.
• The current replication state of a Replication Job belonging to an Optimization is reflected in the column R. If an Optimization is enabled and was executed at least once, it is called applied.
• The visibility State is reflected in the column V

It is recommended to optimize red-colored optimizations first as it might offer the greatest performance boost.

Structure

There are different types of optimizations: table, join and aggregation. Each type represents a particular data projection that is used in the system, like the join of multiple tables, the table itself or the table data processed with an aggregation function.

Optimizations are organized in a tree-like structure. The root elements of the tree are the table/join optimizations and the aggregation groups. Aggregation groups are the sets of aggregation optimizations collected by their match descriptors. The child elements of those groups are the actual optimizations. This structure allows to focus on table and join optimizations, as well as to better manage the groups of aggregation optimizations that target the same source data.

Recommended Optimization's Pop-Up Menu

Right-click on a optimization to

• Enable and Run Optimization: this will apply an optimization, e.g. create / update an appropriate table in the Analytical Storage once (complete load) and redirect all the following queries to this table.
• Enable / Disable: this will enable or disable the redirecting of queries to the Analytical Storage for this optimization. If enabled, optimized data from the Analytical Storage will be returned, otherwise data from the appropriate data sources' tables will be retrieved.
• Run Optimization: this will create / update an appropriate table in the Analytical Storage once (complete load).
• Add Schedule: this will create a Replication Job and add a schedule to it that can be configured in a pop-up dialog.
• Delete Optimization: this will delete the corresponding Recommended Optmization
• Hide Optimization: this will hide the optimization. You can enable or disable the displaying of hidden optimizations by right-clicking on the "V" column header.
• Manage recommended indexes: create, update, delete recommended indexes.

Column filtering

The column filtering can be accessed by right-clicking a particular column. Depending on the data type of the column, different filtering options will be shown and can be selected by clicking on them. Some column filters also contain a text filter that can be used to display only the rows that contain the entered text. Multiple columns can have an active filter simultaneously, in which case only rows that match the criteria of all filters will be displayed.

Optimization's Details

• Optmization ID: unique ID of the recommended optmization
• Title: optimization's "structure". All SQL queries having the same structure are represented by the same recommended optimization. For example queries like select * from myschema.mytable and select mycolumn from myschema.mytable have the same structure myschema.mytable
• Type: type of the optimization's structure, can be Table or Join. The former denotes SQL queries to a single table; the latter SQL queries with JOIN constructs.
• Enabled: state of the Recommended Optimization (enabled or not).
• Materialized: whether the optimization has been materialized or not (i.e., the source data has been replicated in the Analytical Storage).
• Table prefix: contains the prefix with which the replicated tables' names in the Analytical Storage related to this optimization will begin (e.g. "mat_table_0").
• Frequency: denotes how often a SQL query with the same structure was issued to the CData Virtuality Server.
• Last used: the last time a SQL query with this optimization's structure was issued to the CData Virtuality Server.
• Last replication: the last time the data targeted by this optimization was replicated to the Analytical Storage.
• Last replication execuging status: shows if last Replication Job's run was successful or not.
• State of the Source System / Analytical Storage: states if the systems are available or not
• Expected state of the next select query / replication: states if the next replication or select query is expected to fail (e.g. because some systems are not available)

Reference manual pages:

• Optimization Subsystem